﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DONGACS.Common;
using System.Data;

namespace DONGACS.DAO
{
    public class DoanVaoDao  : AccessData
    {
        public DataTable GetDoanVaoByIdReport(string id)
        {
            //CommandText = "Select * from dv_HoSo dv, dr_DanhMucKinhPhi kp ,dr_DanhMucMucDich md  Where dr.IdKinhPhi = kp.Id and dr.IdMucDich = md.Id and dr.Id = " + id;
            CommandText = "Select * from dv_HoSo Where Id = " + id;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }
        public DataTable GetDoanVaoLuuTruYkien(string id, int IdNghiepVu)
        {
            //CommandText = "Select * from dv_HoSo dv, dr_DanhMucKinhPhi kp ,dr_DanhMucMucDich md  Where dr.IdKinhPhi = kp.Id and dr.IdMucDich = md.Id and dr.Id = " + id;
            CommandText = "Select * from dm_LuuTruSoCongVan Where IdHoSo = " + id + "and Nghiepvu = " + IdNghiepVu;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }
        public DataTable GetThanhPhanDoanByIdDoanVaoReport(string id)
        {
            CommandText = "Select * from dv_ThanhPhanDoanVao dv_tp  ,dm_Nuoc d_N Where dv_tp.QuocTich=d_N.Id and  dv_tp.IdDoanVao = " + id;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }

        public DataTable GetDoanVaoByIdBienNhan(string id)
        {
            CommandText = "Select * from dv_HoSo dr  Where  dr.Id = " + id;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }

        public DataTable GetHoSoDinhKemByIdReport(int id)
        {
            CommandText = "Select * from dv_DanhMucLoaiHoSoDinhKem Dm_LDK, dv_CauHinhHoSoDinhKem CH   Where Dm_LDK.Id = CH.IdLoaiHSDK  and CH.IdLoaiDV = " + id;  
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }


        public DataTable SoLuongNguoiThanhPhanDoan(string id)
        {
            CommandText = "Select count(*) as SoLuong from dv_ThanhPhanDoanVao dv_tp  ,dm_Nuoc d_N Where dv_tp.QuocTich=d_N.Id and  dv_tp.IdDoanVao = " + id;
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }

        public DataTable BaoCaoTongHopDoanVao(DateTime TuNgay, DateTime DenNgay)
        {
            CommandText = "select COUNT(a.id)  as TongSoDoanOfMucDich,a.IdMucDich as IdMucDich,(select count(id) from dv_HoSo " +
            "where NgayDi >='" + TuNgay.ToString("yyyy/MM/dd") + "' and NgayDi <= '" + DenNgay.ToString("yyyy/MM/dd") + "') as SoDoanVao,"+
            "b.Ten from dv_HoSo a inner join dv_DanhMucMucDich b on a.IdMucDich = b.Id" +
            " where a.NgayDi >='" + TuNgay.ToString("yyyy/MM/dd") + "' and a.NgayDi <= '" + DenNgay.ToString("yyyy/MM/dd") + "' group by IdMucDich,b.Ten,a.IdMucDich";
            Commandtype = CommandType.Text;
            DataTable table = GetDataTable();
            return table;
        }

    }
}